#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
GitLab核心分析器
专注于年度/月度提交率、代码增量等关键指标的分析
"""

import os
from datetime import datetime
from typing import Dict

import pandas as pd
from loguru import logger

from . import TimescaleDBManager
from .config import get_config


class GitLabCoreAnalyzer:
    """GitLab核心分析器
    
    专注于以下核心指标：
    - annual_commit_rate: 年度提交率
    - month_commit_rate: 月度提交率  
    - annual_total_additions: 年度总新增行数
    - month_total_additions: 月度总新增行数
    """

    def __init__(self, db_manager: TimescaleDBManager = None):
        """初始化分析器"""
        if db_manager is None:
            config = get_config()
            self.db_manager = TimescaleDBManager(config.database.connection_string)
        else:
            self.db_manager = db_manager

    def get_core_metrics(self,
                         gitlab_url: str = None,
                         start_date: datetime = None,
                         end_date: datetime = None,
                         min_active_days: int = 50,
                         use_6month_view: bool = False) -> pd.DataFrame:
        """获取核心指标数据
        
        Args:
            gitlab_url: GitLab实例URL
            start_date: 开始日期
            end_date: 结束日期
            min_active_days: 最小活跃天数
            use_6month_view: 是否使用半年数据视图
            
        Returns:
            包含核心指标的DataFrame
        """
        try:
            # 构建SQL查询
            sql = self._build_core_metrics_query(
                gitlab_url, start_date, end_date, min_active_days, use_6month_view
            )

            # 执行查询
            with self.db_manager.get_connection() as conn:
                df = pd.read_sql(sql, conn)

            logger.info(f"获取到 {len(df)} 条核心指标数据")
            return df

        except Exception as e:
            logger.error(f"获取核心指标失败: {e}")
            return pd.DataFrame()

    def _build_core_metrics_query(self,
                                  gitlab_url: str = None,
                                  start_date: datetime = None,
                                  end_date: datetime = None,
                                  min_active_days: int = 50,
                                  use_6month_view: bool = False) -> str:
        """构建核心指标查询SQL"""

        if use_6month_view:
            # 如果使用半年数据，直接查询半年视图
            base_query = """
            SELECT 
                canonical_name,
                original_names,
                all_emails,
                all_usernames,
                
                -- 核心指标
                annual_commit_rate,
                month_commit_rate,
                annual_total_additions,
                month_total_additions,
                
                -- 基础统计
                total_commits,
                projects_count as total_projects,
                branches_count as total_branches,
                total_additions,
                total_deletions,
                total_changes,
                active_days,
                
                -- 代码质量指标
                avg_additions_per_commit,
                avg_deletions_per_commit,
                avg_changes_per_commit,
                avg_files_per_commit,
                addition_ratio_percent as additions_ratio,
                
                -- 提交类型分布
                feature_commits,
                bugfix_commits,
                doc_commits,
                test_commits,
                refactor_commits,
                style_commits,
                chore_commits,
                
                -- 时间分布统计
                work_hours_commits as work_hour_commits,
                off_hours_commits as non_work_hour_commits,
                weekend_commits,
                weekday_commits,
                work_hours_ratio_percent as work_hour_ratio,
                weekend_ratio_percent as weekend_ratio,
                active_hours_count,
                
                -- 项目贡献度
                project_names,
                significant_projects as important_projects_count,
                avg_commits_per_project as commits_per_project,
                
                -- 一致性指标
                consistency_ratio_percent as consistency_ratio,
                contribution_level,
                
                -- 协作与领导力指标
                collaboration_projects_count,
                avg_project_contribution,
                project_contribution_variance,
                active_years_count,
                projects_per_year,
                avg_additions_per_project,
                
                -- 员工资质信息
                education,
                technical_skill,
                leadership,
                
                -- 时间范围
                first_commit_time as first_commit_date,
                last_commit_time as last_commit_date
                
            FROM v_user_contribution_stats_6m
            WHERE 1=1
            """
        else:
            # 如果使用全年数据，需要联合查询半年数据获取活跃度指标
            base_query = """
            SELECT 
                f.canonical_name,
                f.original_names,
                f.all_emails,
                f.all_usernames,
                
                -- 核心指标（全年）
                f.annual_commit_rate,
                f.month_commit_rate,
                f.annual_total_additions,
                f.month_total_additions,
                
                -- 基础统计（全年）
                f.total_commits,
                f.projects_count as total_projects,
                f.branches_count as total_branches,
                f.total_additions,
                f.total_deletions,
                f.total_changes,
                f.active_days,
                
                -- 半年活跃度指标
                COALESCE(s.annual_commit_rate, 0) as recent_6m_annual_commit_rate,
                COALESCE(s.annual_total_additions, 0) as recent_6m_annual_additions,
                COALESCE(s.active_days, 0) as recent_6m_active_days,
                COALESCE(s.projects_count, 0) as recent_6m_total_projects,
                
                -- 代码质量指标
                f.avg_additions_per_commit,
                f.avg_deletions_per_commit,
                f.avg_changes_per_commit,
                f.avg_files_per_commit,
                f.addition_ratio_percent as additions_ratio,
                
                -- 提交类型分布
                f.feature_commits,
                f.bugfix_commits,
                f.doc_commits,
                f.test_commits,
                f.refactor_commits,
                f.style_commits,
                f.chore_commits,
                
                -- 时间分布统计
                f.work_hours_commits as work_hour_commits,
                f.off_hours_commits as non_work_hour_commits,
                f.weekend_commits,
                f.weekday_commits,
                f.work_hours_ratio_percent as work_hour_ratio,
                f.weekend_ratio_percent as weekend_ratio,
                f.active_hours_count,
                
                -- 项目贡献度
                f.project_names,
                f.significant_projects as important_projects_count,
                f.avg_commits_per_project as commits_per_project,
                
                -- 一致性指标
                f.consistency_ratio_percent as consistency_ratio,
                f.contribution_level,
                
                -- 协作与领导力指标
                f.collaboration_projects_count,
                f.avg_project_contribution,
                f.project_contribution_variance,
                f.active_years_count,
                f.projects_per_year,
                f.avg_additions_per_project,
                
                -- 员工资质信息
                f.education,
                f.technical_skill,
                f.leadership,
                
                -- 时间范围
                f.first_commit_time as first_commit_date,
                f.last_commit_time as last_commit_date
                
            FROM v_user_contribution_stats f
            LEFT JOIN v_user_contribution_stats_6m s ON f.canonical_name = s.canonical_name
            WHERE 1=1
            """

        # 添加过滤条件
        conditions = []

        # 注意：v_user_contribution_stats视图不包含gitlab_url字段，因为它已经聚合了所有实例的数据
        # 如果需要按实例过滤，需要修改视图或使用其他方法

        if start_date:
            conditions.append(f"AND f.last_commit_time >= '{start_date.strftime('%Y-%m-%d')}'")

        if end_date:
            conditions.append(f"AND f.first_commit_time <= '{end_date.strftime('%Y-%m-%d')}'")
        if min_active_days > 0:
            conditions.append(f"AND f.active_days >= {min_active_days}")

        # 组合查询
        full_query = base_query + " " + " ".join(conditions)

        # 按月度提交率排序
        full_query += " ORDER BY f.month_commit_rate DESC, f.annual_commit_rate DESC"

        return full_query

    # 技术能力评估已移除 - 通过git commit无法准确评估技术能力

    def calculate_work_habit_score(self, df: pd.DataFrame) -> pd.DataFrame:
        """计算工作习惯评分
        
        基于时间分布统计，优化后的评分算法更符合实际工作模式
        """
        if df.empty:
            return df

        df = df.copy()

        # 工作时间规律性评分 (0-60分) - 降低权重
        # 优化算法：使用更宽松的评分曲线
        # 工作时间比例在40%-90%之间都给予较高分数
        def calculate_work_time_score(ratio):
            if 50 <= ratio <= 80:  # 最佳范围
                return 60
            elif 40 <= ratio < 50:  # 较低但可接受
                return 36 + (ratio - 40) * 2.4  # 36-60分
            elif 80 < ratio <= 90:  # 较高但可接受
                return 60 - (ratio - 80) * 1.2  # 48-60分
            elif 30 <= ratio < 40:  # 偏低
                return 18 + (ratio - 30) * 1.8  # 18-36分
            elif 90 < ratio <= 100:  # 偏高
                return 48 - (ratio - 90) * 1.8  # 30-48分
            else:  # 极端情况
                return max(6, 30 - abs(ratio - 65) * 0.48)

        df['work_time_score'] = df['work_hour_ratio'].apply(calculate_work_time_score).round(2)

        # 工作日规律性评分 (0-115分) - 提高权重，成为主要评分项
        # 优化算法：工作日比例（100-weekend_ratio）在60%-95%之间都给予较高分数
        def calculate_weekday_score(weekend_ratio):
            weekday_ratio = 100 - weekend_ratio
            if 70 <= weekday_ratio <= 90:  # 最佳范围
                return 115
            elif 60 <= weekday_ratio < 70:  # 较低但可接受
                return 69 + (weekday_ratio - 60) * 4.6  # 69-115分
            elif 90 < weekday_ratio <= 95:  # 较高但可接受
                return 115 - (weekday_ratio - 90) * 3  # 100-115分
            elif 50 <= weekday_ratio < 60:  # 偏低
                return 38 + (weekday_ratio - 50) * 3.1  # 38-69分
            elif 95 < weekday_ratio <= 100:  # 偏高
                return 100 - (weekday_ratio - 95) * 7.5  # 62.5-100分
            else:  # 极端情况
                return max(8, 61 - abs(weekday_ratio - 80) * 0.77)

        df['weekday_score'] = df['weekend_ratio'].apply(calculate_weekday_score).round(2)

        # 活跃时间多样性评分 (0-75分)
        # 优化算法：活跃小时数在3-15小时之间都给予合理分数
        def calculate_active_hours_score(hours):
            if 6 <= hours <= 12:  # 最佳范围
                return 75
            elif 4 <= hours < 6:  # 较少但可接受
                return 45 + (hours - 4) * 15  # 45-75分
            elif 12 < hours <= 16:  # 较多但可接受
                return 75 - (hours - 12) * 5  # 55-75分
            elif 3 <= hours < 4:  # 偏少
                return 25 + (hours - 3) * 20  # 25-45分
            elif 16 < hours <= 20:  # 偏多
                return 55 - (hours - 16) * 5  # 35-55分
            else:  # 极端情况
                return max(10, 50 - abs(hours - 9) * 2)

        df['active_hours_score'] = df['active_hours_count'].apply(calculate_active_hours_score).round(2)

        # 工作习惯总分 (0-250分)
        df['work_habit_score'] = (
                df['work_time_score'] + df['weekday_score'] + df['active_hours_score']
        ).round(2)

        return df

    def calculate_project_participation_score(self, df: pd.DataFrame) -> pd.DataFrame:
        """计算项目参与度评分
        
        重新设计：主要基于月提交率和月新增行数，辅以项目广度
        """
        if df.empty:
            return df

        df = df.copy()
        import numpy as np

        # 1. 月提交率评分 (0-200分) - 提高权重
        # 200次/月为满分标准，100次/月最多100分
        df['monthly_commit_score'] = (
            np.where(df['month_commit_rate'] < 20,
                     df['month_commit_rate'] * 1.0,  # <20次/月: 最多20分
                     np.where(df['month_commit_rate'] < 50,
                              20 + (df['month_commit_rate'] - 20) * 1.33,  # 20-50次/月: 20-60分
                              np.where(df['month_commit_rate'] < 100,
                                       60 + (df['month_commit_rate'] - 50) * 0.8,  # 50-100次/月: 60-100分
                                       np.where(df['month_commit_rate'] < 200,
                                                100 + (df['month_commit_rate'] - 100) * 1.0,  # 100-200次/月: 100-200分
                                                200))))  # >200次/月: 200分满分
        ).round(2)

        # 2. 月新增行数评分 (0-200分) - 根据技术能力等级分层评分，提高权重
        def calculate_additions_score(row):
            additions = row['month_total_additions']
            technical_skill = row.get('technical_skill', 50)  # 默认50分

            if technical_skill > 85:  # 架构师级别 - 标准相对宽松
                baseline = 5000  # 及格基准线
                excellent = 10000  # 优秀标准
            elif technical_skill > 50:  # 高级程序员 - 标准适中
                baseline = 30000  # 及格基准线
                excellent = 50000  # 优秀标准
            else:  # 初级程序员 - 标准最严格，防止划水
                baseline = 500000  # 及格基准线
                excellent = 800000  # 优秀标准

            if additions < baseline * 0.5:  # 低于基准线一半
                return additions / (baseline * 0.5) * 60  # 最多60分
            elif additions < baseline:  # 基准线以下
                return 60 + (additions - baseline * 0.5) / (baseline * 0.5) * 60  # 60-120分
            elif additions < excellent:  # 基准线到优秀标准
                return 120 + (additions - baseline) / (excellent - baseline) * 60  # 120-180分
            else:  # 超过优秀标准
                return min(200, 180 + np.log10(additions / excellent) * 20)  # 180-200分

        df['monthly_additions_score'] = df.apply(calculate_additions_score, axis=1).round(2)

        # 3. 项目数量评分 (0-50分) - 调整权重
        df['project_count_score'] = (
            np.minimum(50, df['total_projects'].clip(0, 15) / 15 * 50)
        ).round(2)

        # 项目参与度总分 (0-450分) - 简化评分体系
        df['project_participation_score'] = (
                df['monthly_commit_score'] +  # 月提交率 (0-200分)
                df['monthly_additions_score'] +  # 月新增行数 (0-200分)
                df['project_count_score']  # 项目数量 (0-50分)
        ).round(2)

        return df

    def calculate_collaboration_ability_score(self, df: pd.DataFrame) -> pd.DataFrame:
        """计算协作能力评分
        
        基于多项目协作和技术影响力的综合评估，根据技术技能等级分层评定
        """
        if df.empty:
            return df

        df = df.copy()
        import numpy as np

        # 根据technical_skill分层设定评分标准
        def calculate_collaboration_score_by_skill(row):
            technical_skill = row['technical_skill']
            collaboration_projects = row['collaboration_projects_count']
            projects_per_year = row['projects_per_year']
            avg_additions = row['avg_additions_per_project']
            contribution_variance = row['project_contribution_variance']
            active_years = row['active_years_count']

            if technical_skill > 85:  # 架构师级别 - 标准相对宽松
                # 协作广度评分 (0-120分) - 架构师标准
                breadth_score = (
                    # 多项目协作：10个项目以上为满分
                        min(70, collaboration_projects * 7) +
                        # 年均项目参与：2个/年为满分
                        min(50, projects_per_year / 2 * 50)
                )
                # 技术影响力评分 (0-130分) - 架构师标准
                influence_score = (
                    # 平均项目代码贡献：15000行/项目为满分
                        min(80, avg_additions / 15000 * 80) +
                        # 项目贡献方差：高方差体现领导力
                        min(50, np.log10(max(1, contribution_variance)) * 15)
                )
            elif technical_skill > 50:  # 高级程序员 - 标准适中
                # 协作广度评分 (0-120分) - 高级程序员标准
                breadth_score = (
                    # 多项目协作：15个项目以上为满分
                        min(70, collaboration_projects * 4.67) +
                        # 年均项目参与：3个/年为满分
                        min(50, projects_per_year / 3 * 50)
                )
                # 技术影响力评分 (0-130分) - 高级程序员标准
                influence_score = (
                    # 平均项目代码贡献：20000行/项目为满分
                        min(80, avg_additions / 20000 * 80) +
                        # 项目贡献方差
                        min(50, np.log10(max(1, contribution_variance)) * 17)
                )
            else:  # 初级程序员 - 标准最严格，防止划水
                # 协作广度评分 (0-120分) - 初级程序员标准严格
                breadth_score = (
                    # 多项目协作：20个项目以上为满分
                        min(70, collaboration_projects * 3.5) +
                        # 年均项目参与：4个/年为满分
                        min(50, projects_per_year / 4 * 50)
                )
                # 技术影响力评分 (0-130分) - 初级程序员标准严格
                influence_score = (
                    # 平均项目代码贡献：25000行/项目为满分
                        min(80, avg_additions / 25000 * 80) +
                        # 项目贡献方差：要求更高的贡献一致性
                        min(50, np.log10(max(1, contribution_variance)) * 20)
                )

            return breadth_score, influence_score

        # 应用分层评分
        scores = df.apply(calculate_collaboration_score_by_skill, axis=1, result_type='expand')
        df['collaboration_breadth_score'] = scores[0].round(2)
        df['technical_influence_score'] = scores[1].round(2)

        # 应用员工教育背景和领导力权重
        # 教育背景权重 (30-90分) 转换为0.8-1.2系数 - 降低权重影响
        df['education_multiplier'] = (0.8 + (df['education'] - 30) / 60 * 0.4).clip(0.8, 1.2)
        # 领导力权重 (30-70分) 转换为0.8-1.2系数 - 降低权重影响
        df['leadership_multiplier'] = (0.8 + (df['leadership'] - 30) / 40 * 0.4).clip(0.8, 1.2)
        # 综合权重系数 - 确保最大不超过1.2
        df['collaboration_multiplier'] = ((df['education_multiplier'] + df['leadership_multiplier']) / 2.0).clip(0.8,
                                                                                                                 1.2)

        # 协作基础分 (协作广度 + 技术影响力)
        df['collaboration_base_score'] = (
                df['collaboration_breadth_score'] +
                df['technical_influence_score']
        ).round(2)

        # 协作能力总分 (直接使用基础分，0-250分，但显示为300分制)
        df['collaboration_ability_score'] = (
                df['collaboration_base_score'] * 1.2
        ).clip(0, 300).round(2)

        return df

    def calculate_consistency_score(self, df: pd.DataFrame) -> pd.DataFrame:
        """计算持续性评估评分
        
        基于一致性指标和活跃度分析
        """
        if df.empty:
            return df

        df = df.copy()

        # 活跃天数评分 (0-100分)
        df['active_days_score'] = (
                df['active_days'].clip(0, 365) / 365 * 100
        ).round(2)

        # 一致性比例评分 (0-100分)
        df['consistency_score'] = (
                df['consistency_ratio'].clip(0, 100) / 100 * 100
        ).round(2)

        # 月度提交率评分 (0-50分)
        df['monthly_rate_score'] = (
                df['month_commit_rate'].clip(0, 100) / 100 * 50
        ).round(2)

        # 持续性总分 (0-250分)
        df['consistency_total_score'] = (
                df['active_days_score'] +
                df['consistency_score'] +
                df['monthly_rate_score']
        ).round(2)

        return df

    def calculate_comprehensive_rating(self, df: pd.DataFrame) -> pd.DataFrame:
        """计算综合评分和等级
        
        基于项目参与度、工作习惯、持续性和协作能力的四维综合评估
        """
        if df.empty:
            return df

        df = df.copy()

        # 综合评分 (总分1000分，简化评分体系)
        df['comprehensive_score'] = (
                df['project_participation_score'] +  # 项目参与度 (0-450分)
                df['work_habit_score'] +  # 工作习惯 (0-250分)
                df['consistency_total_score'] +  # 持续性 (0-250分)
                df['collaboration_ability_score']  # 协作能力 (0-300分，应用权重)
        ).round(2)

        # 评级分类 (基于1000分制，调整评级阈值)
        def get_rating(score):
            if score >= 1000:  # 85%+ 
                return 'S级 (卓越)'
            elif score >= 900:  # 70%+
                return 'A级 (优秀)'
            elif score >= 700:  # 56%+
                return 'B级 (良好)'
            elif score >= 560:  # 42%+
                return 'C级 (一般)'
            elif score >= 280:  # 28%+
                return 'D级 (较差)'
            else:
                return 'E级 (很差)'

        df['comprehensive_rating'] = df['comprehensive_score'].apply(get_rating)

        # 贡献等级 (基于1000分制，调整等级阈值)
        def get_contribution_grade(score):
            if score >= 1000:  # 80%+ 
                return '核心领导者'
            elif score >= 680:  # 68%+
                return '核心贡献者'
            elif score >= 560:  # 56%+
                return '重要贡献者'
            elif score >= 440:  # 44%+
                return '活跃贡献者'
            elif score >= 320:  # 32%+
                return '一般贡献者'
            elif score >= 200:  # 20%+
                return '初级贡献者'
            else:
                return '新手贡献者'

        df['contribution_grade'] = df['comprehensive_score'].apply(get_contribution_grade)

        return df

    def get_comprehensive_analysis(self,
                                   gitlab_url: str = None,
                                   start_date: datetime = None,
                                   end_date: datetime = None,
                                   min_active_days: int = 50,
                                   use_6month_view: bool = False) -> pd.DataFrame:
        """获取综合分析结果
        
        包含四个维度的评估：项目参与度、工作习惯、持续性、协作能力
        """
        # 获取核心指标
        df = self.get_core_metrics(gitlab_url, start_date, end_date, min_active_days, use_6month_view)

        if df.empty:
            return df

        # 计算各维度评分
        df = self.calculate_work_habit_score(df)
        df = self.calculate_project_participation_score(df)
        df = self.calculate_consistency_score(df)
        df = self.calculate_collaboration_ability_score(df)
        df = self.calculate_comprehensive_rating(df)

        return df

    def get_top_contributors(self,
                             gitlab_url: str = None,
                             start_date: datetime = None,
                             end_date: datetime = None,
                             min_active_days: int = 50,
                             limit: int = None,
                             use_6month_view: bool = False) -> pd.DataFrame:
        """获取顶级贡献者列表
        
        按综合评分倒序排列
        """
        # 如果limit为None，从环境变量读取默认值
        if limit is None:
            limit = int(os.getenv('GITLAB_DEFAULT_MAX_CONTRIBUTORS', '50'))

        df = self.get_comprehensive_analysis(gitlab_url, start_date, end_date, min_active_days, use_6month_view)

        if df.empty:
            return df

        # 按综合评分倒序排列
        df = df.sort_values('comprehensive_score', ascending=False)

        return df.head(limit)

    def get_summary_stats(self,
                          gitlab_url: str = None,
                          start_date: datetime = None,
                          end_date: datetime = None,
                          min_active_days: int = 50,
                          use_6month_view: bool = False) -> Dict:
        """获取汇总统计信息"""
        df = self.get_comprehensive_analysis(gitlab_url, start_date, end_date, min_active_days, use_6month_view)

        if df.empty:
            return {}

        return {
            'total_contributors': len(df),
            'avg_monthly_commit_rate': df['month_commit_rate'].mean(),
            'avg_annual_additions': df['annual_total_additions'].mean(),
            'avg_comprehensive_score': df['comprehensive_score'].mean(),
            'rating_distribution': df['comprehensive_rating'].value_counts().to_dict(),
            'top_monthly_rate': df['month_commit_rate'].max(),
            'top_annual_additions': df['annual_total_additions'].max()
        }

    def get_trend_analysis(self,
                           gitlab_url: str = None,
                           start_date: datetime = None,
                           end_date: datetime = None,
                           min_active_days: int = 50) -> pd.DataFrame:
        """获取趋势分析结果
        
        比较全年数据和半年数据，计算各维度的变化趋势
        """
        # 获取全年数据
        df_full = self.get_comprehensive_analysis(gitlab_url, start_date, end_date, min_active_days,
                                                  use_6month_view=False)
        # 获取半年数据
        df_6m = self.get_comprehensive_analysis(gitlab_url, start_date, end_date, min_active_days, use_6month_view=True)

        if df_full.empty or df_6m.empty:
            return pd.DataFrame()

        # 合并数据，以canonical_name为键
        df_trend = df_full[['canonical_name']].copy()

        # 计算各维度的趋势
        trend_metrics = [
            ('project_participation_score', '项目参与度'),
            ('work_habit_score', '工作习惯'),
            ('consistency_total_score', '持续性'),
            ('collaboration_ability_score', '协作能力'),
            ('comprehensive_score', '综合评分')
        ]

        for metric, name in trend_metrics:
            # 合并全年和半年数据
            full_data = df_full[['canonical_name', metric]].rename(columns={metric: f'{metric}_full'})
            six_month_data = df_6m[['canonical_name', metric]].rename(columns={metric: f'{metric}_6m'})

            df_trend = df_trend.merge(full_data, on='canonical_name', how='left')
            df_trend = df_trend.merge(six_month_data, on='canonical_name', how='left')

            # 计算趋势指标
            df_trend[f'{metric}_trend_ratio'] = (
                    df_trend[f'{metric}_6m'] / df_trend[f'{metric}_full'].clip(lower=0.1) * 2  # 乘以2是因为半年数据需要年化
            ).round(3)

            # 趋势分类
            def get_trend_category(ratio):
                if ratio >= 1.2:
                    return '显著提升'
                elif ratio >= 1.1:
                    return '明显提升'
                elif ratio >= 1.05:
                    return '轻微提升'
                elif ratio >= 0.95:
                    return '基本稳定'
                elif ratio >= 0.85:
                    return '轻微下降'
                elif ratio >= 0.7:
                    return '明显下降'
                else:
                    return '显著下降'

            df_trend[f'{metric}_trend'] = df_trend[f'{metric}_trend_ratio'].apply(get_trend_category)

        # 添加基础信息
        df_trend = df_trend.merge(
            df_full[['canonical_name', 'original_names', 'comprehensive_rating', 'contribution_grade']],
            on='canonical_name',
            how='left'
        )

        return df_trend
